package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Part;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Part Data Access Object
 * @author Jason Whiting
 */
public class PartDAO
{

    /////////////////////////////////////////////
    ///   Singleton code
    private static PartDAO instance = null;

    /** Creates a new instance of PartDAO */
    private PartDAO()
    {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized PartDAO getInstance()
    {
        if (instance == null)
        {
            instance = new PartDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new part in the database */
    public Part create(String id) throws DataException
    {
        Part part = new Part(id);
        part.setObjectAlreadyInDB(false);
        Cache.getInstance().put(part.getId(), part);
        return part;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing part from the database     */
    public Part read(String id) throws DataException
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Part) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try
        {
            return read(id, conn);
        } catch (Exception e)
        {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally
        {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing part from the database
     * @return part
     */
    synchronized Part read(String id, Connection conn) throws Exception
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Part) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM part where id=?");

        try
        {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Part with id '" + id + "' not found.");
        } finally
        {
            stmt.close();
        }
    }//read

    /** Internal method to create a part object from a record  */
    synchronized Part readRecord(ResultSet rs, Connection conn) throws Exception
    {
        if (Cache.getInstance().containsKey(rs.getString("id")))
        {
            return (Part) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new part object
        Part part = new Part(rs.getString("id"));

        // Part is already in DB
        part.setObjectAlreadyInDB(true);

        // Put part in cache
        Cache.getInstance().put(part.getId(), part);

        // Set Part Attributes
        part.setManufacturerId(rs.getString("manufacturer_id"));
        part.setName(rs.getString("name"));
        part.setPartNumber(rs.getString("part_number"));
        part.setPrice(rs.getDouble("price"));

        // Part is dirty and needs to be saved to DB
        part.setDirty(false);

        return part;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing part in the database */
    public void save(Part part) throws DataException
    {
        // get a connection from the pool, save all of the sales first using the other part method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try
        {
            save(part, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e)
        {
            try
            {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2)
            {
                throw new DataException("Could not roll back the part database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the part object information.", e);
        } finally
        {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a part in the database */
    void save(Part part, Connection conn) throws Exception
    {
        // put the part object to cache
        Cache.getInstance().put(part.getId(), part);

        // decide whether to update or insert into the database
        if (part.isObjectAlreadyInDB() && part.isDirty())
        {
            update(part, conn);
        } else if (part.isDirty())
        {
            insert(part, conn);
        }//if
    }

    /** External method to update or create a part in the database without committing connection */
    public void saveNoCommit(Part part, Connection conn, Boolean alreadyInDB) throws Exception
    {
        // decide whether to update or insert into the database
        if (alreadyInDB)
        {
            update(part, conn);
        } else
        {
            insert(part, conn);
        }
    }

    /** Saves an existing part to the database */
    private void update(Part part, Connection conn) throws Exception
    {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE part SET manufacturer_id=?, name=?, part_number=?, price=?  WHERE id=?");
        try
        {
            // Get Part Attributes and add them to the statement
            stmt.setString(1, part.getManufacturerId());
            stmt.setString(2, part.getName());
            stmt.setString(3, part.getPartNumber());
            stmt.setDouble(4, part.getPrice());
            stmt.setString(5, part.getId());
            stmt.execute();

            // Part is clean!
            part.setDirty(false);
        } finally
        {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new part into the database */
    private void insert(Part part, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO part (id, manufacturer_id, name, part_number, price) VALUES (?, ?, ?, ?, ?)");
        try
        {
            // Set Part Attributes
            stmt.setString(1, part.getId());
            stmt.setString(2, part.getManufacturerId());
            stmt.setString(3, part.getName());
            stmt.setString(4, part.getPartNumber());
            stmt.setDouble(5, part.getPrice());
            stmt.execute();

            part.setObjectAlreadyInDB(true); // now in DB
            part.setDirty(false); // object is clean
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Part part) throws DataException
    {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Internal method to search by certain fields */
    private List<Part> search(PreparedStatement stmt, Connection conn) throws Exception
    {
        List<Part> tasks = new ArrayList<Part>();

        try
        {
            ResultSet rs = stmt.executeQuery();
            while (rs.next())
            {
                tasks.add(readRecord(rs, conn));
            }
        } finally
        {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }

        return tasks;
    }

    /* Allow user to search by either first or last name or phone */
    public List<Part> getAll() throws Exception
    {
        //establish connection and prepared statement
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT * from part ");

        // run stmt through the search method, return a list of customers matching any name or phone number
        return search(stmt, conn);
    }

    /* Allow user to search by either first or last name or phone */
    public List<Part> getByServiceFacility(String serviceFacilityId) throws Exception
    {
        //establish connection and prepared statement
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT p.id, p.name, p.manufacturer_id, p.part_number, p.price FROM part p, parts_at_service_facility psf WHERE p.id = psf.part_id AND psf.service_facility_id =? ");

        stmt.setString(1, serviceFacilityId);
        // run stmt through the search method, return a list of customers matching any name or phone number
        return search(stmt, conn);
    }
}
